package com.wanmait.hotelManage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.HotelComment;
import com.wanmait.hotelManage.vo.Pager;
import com.wanmait.hotelManage.vo.Userinfo;

public class HotelCommentDAO {
	private HotelCommentDAO() {};
	private static HotelCommentDAO hotelcommentdao=new HotelCommentDAO();
	public static HotelCommentDAO getHotelcommentdao() {
		return hotelcommentdao;
	}
	//�������жԾƵ������
	public List< HotelComment> hotelComment() {
		Connection conn=null;
		PreparedStatement ps=null;
	     ResultSet rs=null;
	     StringBuilder sb=new StringBuilder();
	     sb.append(" select hotel_comment.id,hotel_comment.Userinfo_id ")
	       .append(",hotel_comment.content ")
	       .append(",hotel_comment.input_time,hotel_comment.enable ")
	       .append(",userinfo.id,userinfo.username ")
	       .append(",userinfo.password,userinfo.pic,userinfo.gender ")
	       .append(",userinfo.telephone_number, ")
	       .append(" userinfo.input_time,userinfo.city_id ")
	       .append(",userinfo.enable FROM  ")
	       .append(" userinfo , hotel_comment  ")
	       .append(" WHERE userinfo.id=hotel_comment.userinfo_id");
	     String sql=sb.toString();
	     
	     List<HotelComment> hotelComments=new ArrayList<HotelComment>();
	     try {
	    	 conn=DBHelper.getDBHelper().getConn();
	    	 ps=conn.prepareStatement(sql);
	    	 rs=ps.executeQuery();
	    	 while(rs.next()) {
	    	Userinfo userinfo=new Userinfo();
	    	userinfo.setEnable(rs.getBoolean("userinfo.enable"));
	    	userinfo.setGender(rs.getBoolean("userinfo.gender"));
	    	userinfo.setId(rs.getInt("userinfo.id"));
	    	userinfo.setInputTime(rs.getTimestamp("userinfo.input_time"));
	    	userinfo.setPassword(rs.getString("userinfo.password"));
	    	userinfo.setPic(rs.getString("userinfo.pic"));
	    	userinfo.setTelephoneNumber(rs.getString("userinfo.telephone_number"));
	    	userinfo.setUsername(rs.getString("userinfo.username"));
	    	HotelComment hotelComment=new HotelComment();
	    	hotelComment.setUserinfo(userinfo);
	    	hotelComment.setContent(rs.getString("hotel_comment.content"));
	    	hotelComment.setEnable(rs.getBoolean("hotel_comment.enable"));
	    	hotelComment.setId(rs.getInt("hotel_comment.id"));
	    	hotelComment.setInputTime(rs.getTimestamp("hotel_comment.input_time"));
	    	hotelComments.add(hotelComment);
	    	System.out.println(hotelComment.getId());
	    	 }
			
		} catch (Exception e) {
			
		}
	     finally {
	    	 DBHelper.getDBHelper().close(rs, ps, conn);
	     }
	     System.out.println();
	     return hotelComments;
	}
	/**
	 * 评论审核查找全部
	 * @param pager 
	 * @return
	 */
	public List<HotelComment> findall(Pager pager){
		List<HotelComment>HotelComments=new ArrayList<HotelComment>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT hotel_comment.id,hotel_comment.content,hotel_comment.input_time,userinfo.username,userinfo.pic,userinfo.telephone_number,hotel_comment.read,hotel_comment.enable ");
		sb.append(" from hotel_comment,userinfo  ");
		sb.append(" WHERE hotel_comment.userinfo_id=userinfo.id AND hotel_comment.read=0 AND hotel_comment.ENABLE=1 order by hotel_comment.input_time desc limit ?,?");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			HotelComment hc=null;
			Userinfo uf=null;
			while(rs.next()) {
				hc=new HotelComment();
				hc.setId(rs.getInt("hotel_comment.id"));
				hc.setContent(rs.getString("hotel_comment.content"));
				hc.setInputTime(rs.getTimestamp("hotel_comment.input_time"));
				hc.setRead(rs.getBoolean("hotel_comment.read"));
				hc.setEnable(rs.getBoolean("hotel_comment.enable"));
				uf=new Userinfo();
				uf.setUsername(rs.getString("userinfo.username"));
				uf.setTelephoneNumber(rs.getString("userinfo.telephone_number"));
				uf.setPic(rs.getString("userinfo.pic"));
				hc.setUserinfo(uf);
				HotelComments.add(hc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return HotelComments;
	}
//	public static void main(String[] args) {
//		HotelCommentDAO.getHotelcommentdao().update(1);
//	}
	/**
	 * 删除
	 * @param id
	 */
	public void update(Integer id) {
		String sql="update hotel_comment set enable=0,hotel_comment.read=1 where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, id);
	}
	/**
	 * 批量删除
	 * @param ids
	 */
	public void del(String ids) {
		String sql="update hotel_comment set enable=0,hotel_comment.read=1 where id in("+ids+")";
		DBHelper.getDBHelper().executeUpdate(sql);
		
	}
	/**
	 * 发布的更新
	 * @param id
	 */
	public void release(Integer id) {
		String sql="update hotel_comment set hotel_comment.read=1 where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, id);
	}
	/**
	 * 批量发布更新
	 * @param ids
	 */
	public void releases(String ids) {
		// TODO Auto-generated method stub
		String sql="update hotel_comment set hotel_comment.read=1 where id in("+ids+")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}
	/**
	 * 获取行数
	 * @return
	 */
	public int getrow(){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) c from hotel_comment,userinfo where hotel_comment.read=0 AND hotel_comment.ENABLE=1 and hotel_comment.userinfo_id=userinfo.id");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			rs=ps.executeQuery();
			rs.next();
			Integer count=rs.getInt("c");
			return count;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
	public int getrows(){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) c from hotel_comment,userinfo where hotel_comment.read=1 AND hotel_comment.ENABLE=1 and hotel_comment.userinfo_id=userinfo.id");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			rs=ps.executeQuery();
			rs.next();
			Integer count=rs.getInt("c");
			return count;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
//	public static void main(String[] args) {
//		Pager pager=new Pager();
//		pager.setPageNo(1);
//		pager.setPageSize(6);
//		pager.setPageCount(8);
//		 List<HotelComment>hotelComment=HotelCommentDAO.getHotelcommentdao().findall(pager);
//		 for (HotelComment hotelComment2 : hotelComment) {
//			System.out.println(hotelComment2.getContent());
//		}
//	}
	/**
	 * 前台评论显示
	 * @param pager
	 * @return
	 */
	public List<HotelComment> findAll(Pager pager) {
		List<HotelComment>HotelComments=new ArrayList<HotelComment>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT hotel_comment.id,hotel_comment.content,hotel_comment.input_time,userinfo.username,userinfo.pic,userinfo.telephone_number,hotel_comment.read,hotel_comment.enable ");
		sb.append(" from hotel_comment,userinfo  ");
		sb.append(" WHERE hotel_comment.userinfo_id=userinfo.id AND hotel_comment.read=1 AND hotel_comment.ENABLE=1 and hotel_comment.parent_id=0 order by hotel_comment.input_time desc limit ?,?");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			HotelComment hc=null;
			Userinfo uf=null;
			while(rs.next()) {
				hc=new HotelComment();
				hc.setId(rs.getInt("hotel_comment.id"));
				hc.setContent(rs.getString("hotel_comment.content"));
				hc.setInputTime(rs.getTimestamp("hotel_comment.input_time"));
				hc.setRead(rs.getBoolean("hotel_comment.read"));
				hc.setEnable(rs.getBoolean("hotel_comment.enable"));
				uf=new Userinfo();
				uf.setUsername(rs.getString("userinfo.username"));
				uf.setTelephoneNumber(rs.getString("userinfo.telephone_number"));
				uf.setPic(rs.getString("userinfo.pic"));
				hc.setUserinfo(uf);
				HotelComments.add(hc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return HotelComments;
	}
	/**
	 * 模糊查询搜索用的行数
	 * @param text
	 * @return
	 */
	public int getrowss(String text) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) c from hotel_comment,userinfo where hotel_comment.read=0 and userinfo.username like '%"+text+"%' AND hotel_comment.userinfo_id=userinfo.id AND hotel_comment.ENABLE=1");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			rs=ps.executeQuery();
			rs.next();
			Integer count=rs.getInt("c");
			return count;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
//	public static void main(String[] args) {
//		int rows=HotelCommentDAO.getHotelcommentdao().getrowss("a");
//		System.out.println(rows);
//	}
	/**
	 * 搜索
	 * @param text
	 * @param pager
	 * @return
	 */
	public List<HotelComment> findusername(String text, Pager pager) {
		List<HotelComment>HotelComments=new ArrayList<HotelComment>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT hotel_comment.id,hotel_comment.content,hotel_comment.input_time,userinfo.username,userinfo.pic,userinfo.telephone_number,hotel_comment.read,hotel_comment.enable ");
		sb.append(" from hotel_comment,userinfo  ");
		sb.append(" WHERE hotel_comment.userinfo_id=userinfo.id AND hotel_comment.read=0 AND hotel_comment.ENABLE=1 and userinfo.username like '%"+text+"%' order by hotel_comment.input_time desc limit ?,?");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			HotelComment hc=null;
			Userinfo uf=null;
			while(rs.next()) {
				hc=new HotelComment();
				hc.setId(rs.getInt("hotel_comment.id"));
				hc.setContent(rs.getString("hotel_comment.content"));
				hc.setInputTime(rs.getTimestamp("hotel_comment.input_time"));
				hc.setRead(rs.getBoolean("hotel_comment.read"));
				hc.setEnable(rs.getBoolean("hotel_comment.enable"));
				uf=new Userinfo();
				uf.setUsername(rs.getString("userinfo.username"));
				uf.setTelephoneNumber(rs.getString("userinfo.telephone_number"));
				uf.setPic(rs.getString("userinfo.pic"));
				hc.setUserinfo(uf);
				HotelComments.add(hc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return HotelComments;
	}
	}
